Welcome to Facts and Dimensional Modeling. After watching this video, you will be able
to: Define what facts are in context of data warehousing. Define what a fact table is. Define what dimensions are in context of data
warehousing. Define what a dimension table is. Describe an example of a fact and its dimensions. Data can be lumped into two categories: Facts
and dimensions. Facts are usually quantities which can be
measured, such as temperature, number of sales, or millimeters of rainfall. But facts can also be qualitative in nature. Dimensions are attributes which can be assigned
to facts. Dimensions provide context to facts, which
makes facts useful. For example, a temperature such as “24 degrees
celsius,” all by itself, is not meaningful information. Let’s look at a familiar example. Here we
have a weather report obtained by googling “weather in Kuala Lumpur.” The facts here are things like the temperature,
humidity, probability of precipitation, and wind speed. Notice that these are all quantities. But there are other facts here, such as this
icon, which means “partly cloudy,” or the statement “Clear with periodic clouds,”
and these other icons which indicate forecasted conditions, such as thundershowers. All of these non-numeric facts are examples
of qualitative facts. To make sense of these facts, we need to provide
context. Context is provided by dimensions, which includes
things like the location, “Kuala Lumpur, Malaysia” and the day and time, “Tuesday
at 3 a.m.” The statement “24°C in Kuala Lumpur, Malaysia
on Tuesday, August 17th at 3:00 a.m.” means something. A fact table typically consists of the facts
of a business process, and it also contains foreign keys which establish
well-defined links to dimension tables. Usually, facts are additive measures or metrics,
such as dollar amounts for individual sales transactions. A fact table can contain detail level facts,
such as individual sales transactions, or facts that have been aggregated, such as daily
or weekly sales totals. Fact tables that contain aggregated facts
are called summary tables. For example, you could summarize sales transactions
by summing overall sales for each quarter of the year. An example of a foreign key might be ‘store
ID.’ “Accumulating snapshot” fact tables are
used to record events that take place during a well-defined business process. For example, suppose you have finished configuring
a custom computer for yourself online, and you have just placed your order. The order date and the order amount are recorded
in a snapshot table by the manufacturer. A unique “order ID” is also assigned. Once your order is verified, your payment
is processed. The “amount paid” and the “date paid”
are then recorded. After payment verification, the computer specifications
are sent to the manufacturing department. Once the computer is in production, the “build
start-date” is entered, and once the computer is built, the “build end-date” is recorded.
Finally, once your computer is ready to be sent, the “ship date” is recorded. All these fields are stored in a single row
of the table, which is uniquely identified by the “order ID.” A dimension is a variable that categorizes
facts, Dimensions are called “categorical variables”
by statisticians and machine learning engineers. Dimensions enable users to answer business
questions. The main uses for dimensions in analytics
include filtering, grouping, and labeling operations. For example, commonly used dimensions include
names of people, products, and places, and date or time stamps. A dimension table thus stores the dimensions
of a fact and is joined to the fact table via a foreign
key. Some examples of various types of dimension
tables include: Product tables, which describe products, such
as make, model, color, and size. Employee tables, which describe employees,
such as name, title, and department. Temporal tables, which describe time at the
level of granularity, or precision, at which events are recorded. Geography tables, for location data such as
country, state, city, and postal or zip code. Let us look at an example of a schema with
both fact and dimension tables to help illustrate their relationships. We could have a fact table for recording sales
at a car dealership This table would store facts about each
car sale such as the Sale date, and Sale amount as well as a primary key “Sale ID.” For each sales transaction, we also need to
record its dimensions like the Vehicle sold and the Salesperson who sold it. The attributes of these dimensions, such as
the vehicle Make and Model or the Salesperson's First and Last name are stored in separate
Vehicle and Salesperson tables However, we link them with the Sales table
by recording the “Vehicle ID” and “Salesperson ID” as foreign keys in the Fact table. This way we typically end up with multiple
dimension tables for each fact table. In this video, you learned that: Business data falls into two categories: Facts
and dimensions. Facts are usually numerical measures of business
processes, such as sale amounts in dollars. Dimensions such as ‘sold by’ and ‘store
ID’ categorize facts, such as who sold the product, when, and which store it was
sold from. Dimensions are categorical variables that
provide context for facts and are used for filtering, grouping, and labeling. Facts and dimension tables are linked together
by foreign and primary keys.